package DB;

import java.io.Serializable;
import Entity.Report;

/** this class asks queries to get and insert reprot's details from and into the db  */
public class ReportQuery implements Serializable{
	
	private static final long serialVersionUID = 42L;
	
	/**function which get customer id from db*/
	public static String getCustomersList()
	{
		String str= "SELECT DISTINCT(CustomerID) FROM cst_history";
		return str;
	}
	
	/** count function*/
	public static String getCustomersListTotalBought()
	{
		String str= "SELECT DISTINCT(CustomerID) , count(ProductBought) AS ProductsTotal , count(CustomerResponse) AS ResponseTotal, count(SaleID) As Total"
				+ " FROM cst_history"
				+ " WHERE ContactCustomer is not null"
				+ " GROUP  BY CustomerID" 
				+ " ORDER BY ProductsTotal" ;
		return str;
	}
	
	/**function which get reports's details from db for this parameter
	 * 
	 * @param ReportID
	 * @return
	 */
	public static String getSearchReportQuery( int ReportID)
	{
		String str= "SELECT * FROM repots WHERE ReportID="+ ReportID +" ";
		return str;
	}
	
	/**function which insert report's details into db
	 * 
	 * @param Rpt
	 * @return
	 */
	public static String getInsertReportQuery( Report Rpt)
	{
		String str= "INSERT INTO reports (ReportID,ReportType,ReportDate) VALUES ("+ Rpt.getReportId()
				+ ",\""
				+ Rpt.getReportType()
				+ "\",\""
				+ Rpt.getReportDate()
				+ "\")";
		return str;
	}
	
	/**function which get report's details from db where sale id like inserted
	 * 
	 * @param SaleID
	 * @return
	 */
	public static String getExistReportSale( String SaleID)
	{
		String str= "SELECT * FROM cst_history WHERE SaleID="+SaleID+"";
			;
		return str;
	}
	
	/**function which get report's call time from db where sale id like inserted
	 * 
	 * @param SaleID
	 * @return
	 */
	public static String getTotalTimeCommentReport(String SaleID)
	{
		String str="SELECT (CallTimeMin) FROM cst_history WHERE SaleID="
				+ SaleID
				+ " AND CustomerApproval=  1 " ;
		return str;
	}
	
	/**function which count number of customers from db where sale id like inserted
	 * 
	 * @param SaleID
	 * @return
	 */
	public static String getTotalCustomersCommentReport(String SaleID)
	{
		String str="SELECT COUNT(CustomerID) FROM cst_history "+" WHERE SaleID="
				+ SaleID
				+ " "+" AND CustomerApproval=1";
		return str;
	}
	
	/**function which count number of sales from db where sale id like inserted
	 * 
	 * @param SaleID
	 * @return
	 */
	public static String getTotalSalesCommentReport(String SaleID)
	{
		String str="SELECT COUNT(SaleID) FROM cst_history "+" WHERE SaleID="
				+ SaleID
				+ " "+" AND CustomerApproval=1";
		return str;
	}
	
	/**function which get customer response from db where sale id like inserted
	 * 
	 * @param SaleID
	 * @return
	 */
	public static String getCommentToCommentReport(String SaleID)
	{
		String str="SELECT (CustomerResponse) FROM cst_history "+" WHERE SaleID="
				+ SaleID
				+ " "+" AND CustomerApproval=0";
		return str;
	}
	
	/**function which get customer response from db where sale id like inserted
	 * 
	 * @param SaleID
	 * @return
	 */
	public static String InsertReport(String SaleID)
	{
		String str="SELECT (CustomerResponse) FROM cst_history "+" WHERE SaleID="
				+ SaleID
				+ " "+" AND CustomerApproval=0";
		return str;
	}
	
	/**function which check if a field is empty */
	public boolean isEmpty(String field) 
	{
		if (field.isEmpty())
			return true;
		return false;
	}

}